package database;

import java.util.ArrayList;
import java.sql.*;

import collection.*;
import mediaitem.*;

public class DatabaseSupport
{
	/* These are just rough ideas of how things should work
	 * in each method. Correct implementations will be done 
	 * later.
	 */
	private static Connection connectToDB(){
		// load jdbc driver
		try{
			String driverName = "com.mysql.jdbc.Driver";
			Class.forName(driverName);
		}
		catch (ClassNotFoundException e){
			// handle exception
			e.printStackTrace();
		}
			
		
		Connection conn = null;
		try {
			// not a valid url.
			// TODO setup database.
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306");
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		
		return conn;
	}
	
	public static Collection getCollection(String collName)
	{
		String name = "";
		Connection conn = null;
		
		try {
			conn = connectToDB();
			String query = "SELECT * FROM Collections WHERE name='" + collName + "';";
			Statement stmt = conn.createStatement();
			// get data from table
			ResultSet item = stmt.executeQuery(query);
			
			name = item.getString(1);
			//parents = item.getString(2);
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return new Collection(name, (new ArrayList<Collection>()));
	}

	public static boolean putCollection(Collection c)
	{
		Connection conn = null;
		
		try {
			conn = connectToDB();
			// TODO figure out a way to add collection to database
			String query = "INSERT INTO Collections VALUES (" + c.getName() + ");";
			Statement stmt = conn.createStatement();
			// get data from table
			stmt.executeUpdate(query);
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return true;
	}

	public static boolean removeCollection(Collection c)
	{
		Connection conn = null;
		boolean returnValue = true;
		
		try{
			conn = connectToDB();
			String query = "DELETE FROM Collections WHERE name='" + c.getName() + "';";
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			
			conn.close();
		} catch(Exception e){
			e.printStackTrace();
			returnValue = false;
		}
		return returnValue;
	}
	
	public static boolean storeMediaItem(MediaItem i){
		Connection conn = null;
		boolean returnValue = true;
		boolean isInDB = false; // check if item is already in database
		
		try {
			conn = connectToDB();
			/* insert new row into Items table
			 * 
			 * Items table's columns: 
			 * name|description|path|type
			 */
			String query = null;
			Statement stmt = conn.createStatement();
			
			// TODO search for item in DB
			// 		isInDB <- true if there is, false otherwise
			
			if(!isInDB){
				query = "INSERT INTO Items VALUES (" + i.getName()+","+i.getDescription()
						+","+i.getPath()+","+i.getType()+");"; 
				stmt.executeUpdate(query);
			}
			else{
				
			}
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			returnValue = false;
		}
		
		return returnValue;
	}
	
	public static MediaItem getMediaItem(String itemName){
		String name = "", desc = "", path = "";
		Connection conn = null;
		
		try {
			conn = connectToDB();
			String query = "SELECT * FROM Items WHERE name='" + itemName + "';";
			Statement stmt = conn.createStatement();
			// get data from table
			ResultSet item = stmt.executeQuery(query);
			
			// get name from 1st column, description from 2nd
			// path from 3rd.
			name = item.getString(1);
			desc = item.getString(2);
			path = item.getString(3);
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return (new MediaItem(name, desc, path));
	}
	
	public static boolean removeMediaItem(String itemName){
		Connection conn = null;
		boolean returnValue = true;
		
		try{
			conn = connectToDB();
			String query = "DELETE FROM Items WHERE name='" + itemName + "';";
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			
			conn.close();
		} catch(Exception e){
			e.printStackTrace();
			returnValue = false;
		}
		return returnValue;
	}
}
